/**
 * Copyright (C) 2018-2020
 * All rights reserved, Designed By www.yixiang.co
 * 注意：
 * 本软件为www.yixiang.co开发研制，未经购买不得使用
 * 购买后可获得全部源代码（禁止转卖、分享、上传到码云、github等开源平台）
 * 一经发现盗用、分享等行为，将追究法律责任，后果自负
 */
package co.yixiang.modules.user.service.mapper;

import co.yixiang.common.mapper.CoreMapper;
import co.yixiang.modules.user.domain.YxUserBill;
import co.yixiang.modules.user.service.dto.BillOrderRecordDto;
import co.yixiang.modules.user.service.dto.MUserBillDto;
import co.yixiang.modules.user.service.dto.MUserBillOperateDto;
import co.yixiang.modules.user.service.dto.YxUserBillDto;
import co.yixiang.modules.user.vo.BillOperateVo;
import co.yixiang.modules.user.vo.BillVo;
import co.yixiang.modules.user.vo.SignVo;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
* @author hupeng
* @date 2020-05-12
*/
@Repository
public interface UserBillMapper extends CoreMapper<YxUserBill> {

    @Select("select IFNULL(sum(number),0) from yx_user_bill " +
            "where status=1 and type='sign' and pm=1 and category='integral' " +
            "and uid=#{uid}")
    double sumIntegral(@Param("uid") Long uid);

    @Select("SELECT DATE_FORMAT(a.create_time,'%Y-%m-%d') as addTime,a.title,a.number " +
            "FROM yx_user_bill a INNER JOIN yx_user u ON u.uid=a.uid WHERE a.category = 'integral'" +
            " AND a.type = 'sign' AND a.status = 1 AND a.uid = #{uid} " +
            "ORDER BY a.create_time DESC")
    List<SignVo> getSignList(@Param("uid") Long uid, Page page);

    @Select("SELECT o.order_id as orderId,DATE_FORMAT(b.create_time, '%Y-%m-%d %H:%i') as time," +
            "b.number,u.avatar,u.nickname FROM yx_user_bill b " +
            "INNER JOIN yx_store_order o ON o.id=b.link_id " +
            "RIGHT JOIN yx_user u ON u.uid=o.uid" +
            " WHERE b.uid = #{uid} AND ( DATE_FORMAT(b.create_time, '%Y-%m')= #{time} ) AND " +
            "b.category = 'now_money' AND b.type = 'brokerage' ORDER BY time DESC")
    List<BillOrderRecordDto> getBillOrderRList(@Param("time") String time, @Param("uid") Long uid);

    @Select("SELECT DATE_FORMAT(create_time,'%Y-%m') as time " +
            " FROM yx_user_bill ${ew.customSqlSegment}")
    List<String> getBillOrderList(@Param(Constants.WRAPPER) Wrapper<YxUserBill> userWrapper, Page page);

    @Select("SELECT DATE_FORMAT(create_time,'%Y-%m') as time,group_concat(id SEPARATOR ',') ids " +
            " FROM yx_user_bill ${ew.customSqlSegment}")
    List<BillVo> getBillList(@Param(Constants.WRAPPER) Wrapper<YxUserBill> userWrapper, Page page);

    @Select("<script> SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as time,group_concat(id SEPARATOR ',') ids " +
            " FROM yx_user_bill where uid=#{uid} and type in('brokerage','pay_product') and category ='now_money' " +
            "<if test =\"title !=''\">and title=#{title}</if> " +
            "<if test =\"pm !=3\">and pm=#{pm}</if> " +
            "and DATE_FORMAT(create_time,'%Y-%m-%d')=#{day} order by create_time desc </script> ")
    List<BillVo> getBill4dayList(@Param("uid") Long uid,@Param("title") String title,@Param("day") String day,@Param("pm") Integer pm);

    @Select("<script> SELECT DATE_FORMAT(create_time,'%Y-%m') as time,group_concat(id SEPARATOR ',') ids " +
            " FROM yx_user_bill where uid=#{uid} and type in('brokerage','pay_product') and category ='now_money' " +
            "<if test =\"title !=''\">and title=#{title}</if> " +
            "<if test =\"pm !=3\">and pm=#{pm}</if> " +
            "and DATE_FORMAT(create_time,'%Y-%m')=#{month} " +
            "order by create_time desc </script> ")
    List<BillVo> getBill4monthList(@Param("uid") Long uid,@Param("title") String title,@Param("month") String month,@Param("pm") Integer pm);

    @Select("SELECT DATE_FORMAT(create_time,'%Y-%m-%d %H:%i') as add_time,title,number,pm " +
            " FROM yx_user_bill ${ew.customSqlSegment}")
    List<MUserBillDto> getUserBillList(@Param(Constants.WRAPPER) Wrapper<YxUserBill> userWrapper);

    @Select("<script> SELECT DATE_FORMAT(a.create_time,'%Y-%m-%d %H:%i:%s') AS add_time,CASE WHEN a.title in('7','11') and pm=1 then '订单积分' WHEN a.title in('7','11') and pm=0 then '积分消费' WHEN a.title in('15') and pm=0 then '积分转账' when a.title in('12') then '运营积分' when a.title in('13') then '推三返一积分' when a.title in('14') then '业绩奖励' when a.title in('15') then '转账积分' when a.title in('16') then '秒杀积分' when a.title in('17') then '培训费分润积分' when a.title in('18') then '公司转账积分' end title,CASE WHEN a.pm =1 THEN number WHEN a.pm=0 THEN -number END number,a.pm,CASE WHEN a.mark ='订单分润' THEN CONCAT('来自', '市场推广', '的收益') WHEN (a.mark ='积分转账' or (a.title in('18') and a.uid =0)) THEN CONCAT('转账到', a.to_phone, '的积分') WHEN (a.mark ='转账获取积分' or (a.title in('18') and a.uid !=0)) THEN CONCAT('来自', a.to_phone, '的积分') ELSE a.mark END phone  " +
            " FROM yx_user_bill a left join yx_store_order o on o.id =a.link_id left join yx_user u on u.uid=o.uid " +
            "where a.uid=#{uid} and a.type in('brokerage','pay_product') and a.category ='now_money' " +
            "<if test =\"title !=''\">and a.title=#{title}</if> " +
            "<if test =\"pm !=3\">and a.pm=#{pm}</if> " +
            "and DATE_FORMAT(a.create_time,'%Y-%m-%d') =#{day} order by a.create_time desc </script> ")
    List<MUserBillDto> getUserBill4dayList(Page page,@Param("uid") Long uid,@Param("title") String title,@Param("day") String day,@Param("pm") Integer pm);

    @Select("<script> SELECT a.add_time,a.title,IFNULL(SUM(a.number),0) AS number FROM (SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as add_time,'积分明细' as title,CASE WHEN pm =1 THEN number WHEN pm=0 THEN -number END number " +
            " FROM yx_user_bill where uid=#{uid} and type in('brokerage','pay_product') and category ='now_money' " +
            "<if test =\"title !=''\">and title=#{title}</if> " +
            "<if test =\"pm !=3\">and pm=#{pm}</if> " +
            "and DATE_FORMAT(create_time,'%Y-%m') =#{month} " +
            "order by create_time desc)a GROUP BY a.add_time,a.title ORDER BY a.add_time DESC </script> ")
    List<MUserBillDto> getUserBill4monthList(Page page,@Param("uid") Long uid,@Param("title") String title,@Param("month") String month,@Param("pm") Integer pm);

    @Select("select IFNULL(sum(number),0) from yx_user_bill " +
            "where status=1 and type in('brokerage','pay_product') and pm=1 and category='now_money' " +
            "and uid=#{uid}")
    double sumPrice(@Param("uid") int uid);

    @Select("select IFNULL(sum(number),0) from yx_user_bill " +
            "where status=1 and type='recharge' and pm=1 and category='now_money' " +
            "and uid=#{uid}")
    double sumRechargePrice(@Param("uid") Long uid);


    @Select("select IFNULL(sum(number),0) from yx_user_bill " +
            "where status=1 and type='brokerage' and pm=1 and category='now_money' " +
            "and uid=#{uid} and TO_DAYS(NOW()) - TO_DAYS(create_time) <= 1")
    double sumYesterdayPrice(@Param("uid") Long uid);

    @Select("select IFNULL(sum(number),0) from yx_user_bill " +
            "where status=1 and type='brokerage' and pm=1 and category='now_money' " +
            "and uid=#{uid} and DATE_FORMAT(create_time,'%Y-%m-%d') =DATE_FORMAT(NOW(),'%Y-%m-%d')")
    double sumTodayPrice(@Param("uid") Long uid);

    @Select("select IFNULL(sum(number),0) from yx_user_bill " +
            "where status=1 and type='brokerage' and pm=1 and category='now_money' " +
            "and uid=#{uid} and DATE_FORMAT(create_time,'%Y-%m') =DATE_FORMAT(NOW(),'%Y-%m')")
    double sumMonthPrice(@Param("uid") Long uid);

    @Select("select IFNULL(sum(number),0) from yx_user_bill " +
            "where status=1 and type in('brokerage','pay_product') and pm=#{pm} and category='now_money' " +
            "and uid=#{uid} and DATE_FORMAT(create_time,'%Y-%m') =#{month}")
    double sumMonthPrice4detail(@Param("uid") Long uid,@Param("month") String month,@Param("pm") Integer pm);

    @Select("select IFNULL(sum(number),0) from yx_user_bill " +
            "where status=1 and type='brokerage' and pm=1 and category='now_money' " +
            "and uid=#{uid}")
    double sumTotalPrice(@Param("uid") Long uid);

    @Select("<script> select case when b.uid =0 then su.nickname else b.title end title,b.pm,b.category,b.type,b.number,b.create_time ,case when b.uid =0 then '公司账户' else u.nickname end nickname,b.to_phone,b.mark " +
            "from yx_user_bill b left join yx_user u on u.uid=b.uid left join yx_user su on su.uid=b.to_Uid  where 1=1  "  +
            "<if test =\"category !=''\">and b.category=#{category}</if> " +
            "<if test =\"type !=''\">and b.type=#{type}</if> " +
            "<if test =\"title !=''\">and b.title=#{title}</if> " +
            "<if test =\"toPhone !=''\">and b.to_phone  LIKE CONCAT('%',#{toPhone},'%')</if> " +
            "<if test =\"pm !=null\">and b.pm=#{pm}</if> " +
            "<if test =\"uid !=null\">and b.uid=#{uid}</if> " +
            "<if test =\"date !=null\">and b.create_time &gt;= STR_TO_DATE(#{date},'%Y-%m-%d %H:%i:%s')</if> " +
            "<if test =\"date1 !=null\">and b.create_time &lt;=STR_TO_DATE(#{date1},'%Y-%m-%d %H:%i:%s')</if> " +
            "<if test =\"nickname !=''\">and u.nickname LIKE CONCAT('%',#{nickname},'%')</if> order by b.create_time desc </script> ")
    List<YxUserBillDto> findAllByQueryCriteria(@Param("category") String category, @Param("type") String type, @Param("nickname") String nickname, @Param("pm") Integer pm, @Param("date")String date, @Param("date1")String date1,@Param("title")String title, @Param("uid") Long uid,@Param("toPhone") String toPhone);

    @Select("SELECT DATE_FORMAT(yub.create_time,'%Y-%m') as time,group_concat(yub.id SEPARATOR ',') ids " +
            " FROM yx_user_bill yub " +
            " LEFT JOIN yx_store_order yso ON yso.id = yub.link_id" +
            " LEFT JOIN yx_store_order_cart_info b ON b.oid =yso.id" +
            " LEFT JOIN yx_store_cart c ON c.id =b.cart_id" +
            " LEFT JOIN yx_system_city ysc ON ysc.city_id =yso.province_id" +
            " LEFT JOIN yx_system_city ysci ON ysci.city_id =yso.city_id " +
            " where yub.category='now_money' AND yub.type in('brokerage','pay_product') AND yub.title ='12' AND yub.uid=#{uid}  " +
            " and DATE_FORMAT(yub.create_time,'%Y-%m')=#{month}  and yso.province_id  =#{areaId} " +
            "GROUP BY ysci.name,DATE_FORMAT(yub.create_time,'%Y-%m') order by yub.create_time desc")
    List<BillOperateVo> getOperateCommission4monthList(@Param("uid") Long uid, @Param("month") String month, @Param("areaId") Long areaId);

    @Select("SELECT ysci.name AS area_name,ysci.city_id AS area_id,DATE_FORMAT(yub.create_time,'%Y-%m') AS add_time, " +
            "SUM(IFNULL(yub.number,0)) AS current_month_sum,SUM(IFNULL(c.cart_num,0)) as current_month_amount" +
            " FROM yx_user_bill yub " +
            " LEFT JOIN yx_store_order yso ON yso.id = yub.link_id" +
            " LEFT JOIN yx_store_order_cart_info b ON b.oid =yso.id" +
            " LEFT JOIN yx_store_cart c ON c.id =b.cart_id" +
            " LEFT JOIN yx_system_city ysc ON ysc.city_id =yso.province_id" +
            " LEFT JOIN yx_system_city ysci ON ysci.city_id =yso.city_id " +
            " where yub.category='now_money' AND yub.type ='brokerage' AND yub.title ='12' AND yub.uid=#{uid}  " +
            " and DATE_FORMAT(yub.create_time,'%Y-%m')=#{month}  and yso.province_id  =#{areaId} " +
            "GROUP BY ysci.name,ysci.city_id,DATE_FORMAT(yub.create_time,'%Y-%m') order by yub.create_time desc")
    List<MUserBillOperateDto> getUserOperateCommission4monthList(Page page,@Param("uid") Long uid, @Param("month") String month, @Param("areaId") Long areaId);

    @Select("select IFNULL(sum(yub.number),0) from yx_user_bill yub" +
            " LEFT JOIN yx_store_order yso ON yso.id = yub.link_id" +
            " where yub.category='now_money' AND yub.type ='brokerage' AND yub.title ='12' AND yub.uid=#{uid} " +
            " and yso.province_id  =#{areaId}")
    double operateCommissionSum(@Param("uid") Long uid,@Param("areaId") Long areaId);

    @Select("select IFNULL(sum(yub.number),0) from yx_user_bill yub" +
            " LEFT JOIN yx_store_order yso ON yso.id = yub.link_id" +
            " where yub.category='now_money' AND yub.type ='brokerage' AND yub.title =#{title} AND yub.uid=#{uid} AND yso.combination_id=#{id}")
    double rebateCommissionSum(@Param("uid") Long uid,@Param("title") String title,@Param("id") Long id);

    @Select("select IFNULL(sum(yub.number),0) from yx_user_bill yub" +
            " LEFT JOIN yx_store_order yso ON yso.id = yub.link_id" +
            " where yub.category='now_money' AND yub.type ='brokerage' AND yub.title ='12' AND yub.uid=#{uid} " +
            " and DATE_FORMAT(yub.create_time,'%Y-%m')=#{month} and yso.province_id  =#{areaId}")
    double operate4monthCommissionSum(@Param("uid") Long uid, @Param("month") String month,@Param("areaId") Long areaId);

    @Select("select IFNULL(sum(yub.number),0) from yx_user_bill yub" +
            " LEFT JOIN yx_store_order yso ON yso.id = yub.link_id" +
            " where yub.category='now_money' AND yub.type ='brokerage' AND yub.title ='12' AND yub.uid=#{uid} " +
            " and yso.city_id  =#{areaId}")
    double operateCommissionSum4city(@Param("uid") Long uid,@Param("areaId") Long areaId);

    @Select("select IFNULL(sum(yub.number),0) from yx_user_bill yub" +
            " LEFT JOIN yx_store_order yso ON yso.id = yub.link_id" +
            " where yub.category='now_money' AND yub.type ='brokerage' AND yub.title ='12' AND yub.uid=#{uid} " +
            " and DATE_FORMAT(yub.create_time,'%Y-%m')=#{month} and yso.city_id  =#{areaId}")
    double operate4monthCityCommissionSum(@Param("uid") Long uid, @Param("month") String month,@Param("areaId") Long areaId);

    @Select("select IFNULL(sum(yub.number),0) from yx_user_bill yub" +
            " LEFT JOIN yx_store_order yso ON yso.id = yub.link_id" +
            " where yub.category='now_money' AND yub.type ='brokerage' AND yub.title ='12' AND yub.uid=#{uid} " +
            " and yso.district_id  =#{areaId}")
    double operateCommissionSum4area(@Param("uid") Long uid,@Param("areaId") Long areaId);

    @Select("select IFNULL(sum(yub.number),0) from yx_user_bill yub" +
            " LEFT JOIN yx_store_order yso ON yso.id = yub.link_id" +
            " where yub.category='now_money' AND yub.type ='brokerage' AND yub.title ='12' AND yub.uid=#{uid} " +
            " and DATE_FORMAT(yub.create_time,'%Y-%m')=#{month} and yso.district_id  =#{areaId}")
    double operate4monthAreaCommissionSum(@Param("uid") Long uid, @Param("month") String month,@Param("areaId") Long areaId);



    @Select("SELECT DATE_FORMAT(yub.create_time,'%Y-%m') as time,group_concat(yub.id SEPARATOR ',') ids " +
            " FROM yx_user_bill yub " +
            " LEFT JOIN yx_store_order yso ON yso.id = yub.link_id" +
            " LEFT JOIN yx_store_order_cart_info b ON b.oid =yso.id" +
            " LEFT JOIN yx_store_cart c ON c.id =b.cart_id" +
            " LEFT JOIN yx_system_city ysc ON ysc.city_id =yso.city_id" +
            " LEFT JOIN yx_system_city ysci ON ysci.city_id =yso.district_id " +
            " where yub.category='now_money' AND yub.type ='brokerage' AND yub.title ='12' AND yub.uid=#{uid}  " +
            " and DATE_FORMAT(yub.create_time,'%Y-%m')=#{month}  and yso.city_id  =#{areaId} " +
            "GROUP BY ysci.name,DATE_FORMAT(yub.create_time,'%Y-%m') order by yub.create_time desc")
    List<BillOperateVo> getOperateCommission4monthCityList(@Param("uid") Long uid,@Param("month") String month,@Param("areaId") Long areaId);

    @Select("SELECT ysci.name AS area_name,ysci.city_id AS area_id,DATE_FORMAT(yub.create_time,'%Y-%m') AS add_time, " +
            "SUM(IFNULL(yub.number,0)) AS current_month_sum,SUM(IFNULL(c.cart_num,0)) as current_month_amount" +
            " FROM yx_user_bill yub " +
            " LEFT JOIN yx_store_order yso ON yso.id = yub.link_id" +
            " LEFT JOIN yx_store_order_cart_info b ON b.oid =yso.id" +
            " LEFT JOIN yx_store_cart c ON c.id =b.cart_id" +
            " LEFT JOIN yx_system_city ysc ON ysc.city_id =yso.city_id" +
            " LEFT JOIN yx_system_city ysci ON ysci.city_id =yso.district_id " +
            " where yub.category='now_money' AND yub.type ='brokerage' AND yub.title ='12' AND yub.uid=#{uid}  " +
            " and DATE_FORMAT(yub.create_time,'%Y-%m')=#{month}  and yso.city_id  =#{areaId} " +
            "GROUP BY ysci.name,ysci.city_id,DATE_FORMAT(yub.create_time,'%Y-%m') order by yub.create_time desc")
    List<MUserBillOperateDto> getUserOperateCommission4monthCityList(Page page,@Param("uid") Long uid,@Param("month") String month,@Param("areaId") Long areaId);


    @Select("SELECT DATE_FORMAT(yub.create_time,'%Y-%m-%d') as time,group_concat(yub.id SEPARATOR ',') ids " +
            " FROM yx_user_bill yub " +
            " LEFT JOIN yx_store_order yso ON yso.id = yub.link_id" +
            " LEFT JOIN yx_store_order_cart_info b ON b.oid =yso.id" +
            " LEFT JOIN yx_store_cart c ON c.id =b.cart_id" +
            " LEFT JOIN yx_system_city ysc ON ysc.city_id =yso.district_id" +
            " where yub.category='now_money' AND yub.type ='brokerage' AND yub.title ='12' AND yub.uid=#{uid}  " +
            " and DATE_FORMAT(yub.create_time,'%Y-%m')=#{month}  and yso.district_id  =#{areaId} " +
            "GROUP BY ysc.name,DATE_FORMAT(yub.create_time,'%Y-%m-%d') order by yub.create_time desc")
    List<BillOperateVo> getOperateCommission4monthAreaList(@Param("uid") Long uid,@Param("month") String month,@Param("areaId") Long areaId);

    @Select("SELECT ysc.name AS area_name,ysc.city_id AS area_id,DATE_FORMAT(yub.create_time,'%Y-%m-%d') AS add_time, " +
            "SUM(IFNULL(yub.number,0)) AS current_month_sum,SUM(IFNULL(c.cart_num,0)) as current_month_amount" +
            " FROM yx_user_bill yub " +
            " LEFT JOIN yx_store_order yso ON yso.id = yub.link_id" +
            " LEFT JOIN yx_store_order_cart_info b ON b.oid =yso.id" +
            " LEFT JOIN yx_store_cart c ON c.id =b.cart_id" +
            " LEFT JOIN yx_system_city ysc ON ysc.city_id =yso.district_id" +
            " where yub.category='now_money' AND yub.type ='brokerage' AND yub.title ='12' AND yub.uid=#{uid}  " +
            " and DATE_FORMAT(yub.create_time,'%Y-%m')=#{month}  and yso.district_id  =#{areaId} " +
            "GROUP BY ysc.name,ysc.city_id,DATE_FORMAT(yub.create_time,'%Y-%m-%d') order by yub.create_time desc")
    List<MUserBillOperateDto> getUserOperateCommission4monthAreaList(Page page,@Param("uid") Long uid,@Param("month") String month,@Param("areaId") Long areaId);


    @Select("SELECT DATE_FORMAT(yub.create_time,'%Y-%m-%d') as time,group_concat(yub.id SEPARATOR ',') ids " +
            " FROM yx_user_bill yub " +
            " LEFT JOIN yx_store_order yso ON yso.id = yub.link_id" +
            " LEFT JOIN yx_store_order_cart_info b ON b.oid =yso.id " +
            " LEFT JOIN yx_store_product d ON d.id =b.product_id " +
            " LEFT JOIN yx_store_cart c ON c.id =b.cart_id" +
            " LEFT JOIN yx_system_city ysc ON ysc.city_id =yso.district_id" +
            " where yub.category='now_money' AND yub.type ='brokerage' AND yub.title ='12' AND yub.uid=#{uid}  " +
            " and DATE_FORMAT(yub.create_time,'%Y-%m-%d')=#{month}  and yso.district_id  =#{areaId} " +
            " order by yub.create_time desc")
    List<BillOperateVo> getOperateCommission4dayAreaList(@Param("uid") Long uid,@Param("month") String month,@Param("areaId") Long areaId);

    @Select("SELECT ysc.name AS area_name,d.store_name,e.nickname as user_name,yso.order_id,DATE_FORMAT(yub.create_time,'%Y-%m-%d %h:%i:%s') AS add_time, " +
            "IFNULL(yub.number,0) AS current_month_sum,IFNULL(c.cart_num,0) as current_month_amount" +
            " FROM yx_user_bill yub " +
            " LEFT JOIN yx_store_order yso ON yso.id = yub.link_id" +
            " LEFT JOIN yx_store_order_cart_info b ON b.oid =yso.id" +
            " LEFT JOIN yx_store_product d ON d.id =b.product_id " +
            " LEFT JOIN yx_store_cart c ON c.id =b.cart_id" +
            " LEFT JOIN yx_user e ON yso.uid = e.uid " +
            " LEFT JOIN yx_system_city ysc ON ysc.city_id =yso.district_id" +
            " where yub.category='now_money' AND yub.type ='brokerage' AND yub.title ='12' AND yub.uid=#{uid}  " +
            " and DATE_FORMAT(yub.create_time,'%Y-%m-%d')=#{month}  and yso.district_id  =#{areaId} " +
            " order by yub.create_time desc")
    List<MUserBillOperateDto> getUserOperateCommission4dayAreaList(Page page,@Param("uid") Long uid,@Param("month") String month,@Param("areaId") Long areaId);


}
